04. supabase-admin-users-rpc.sql

원본 파일: 'C:\Repository\loafacto-hub\docs\web-ui\04. supabase-admin-users-rpc.sql'

-- =============================================================================
-- 관리자용: 전체 사용자 목록 조회 RPC
-- get_my_role / user_roles / profiles 적용 후, Supabase SQL Editor에서 실행하세요.
-- profile_status 사용 시 15. supabase-withdraw-account.sql(profiles.status 컬럼) 적용 후 실행하세요.
-- 호출자는 super_admin 또는 operator 여야 하며, auth.users + profiles + user_roles를 조인해 반환합니다.
-- signup_provider: 초기 가입 경로(raw_app_meta_data.provider), linked_providers: 연동된 로그인 경로들(auth.identities 기준, 쉼표 구분)
-- =============================================================================

-- 반환 타입(OUT/테이블 컬럼)이 바뀌면 CREATE OR REPLACE가 불가하므로, 기존 함수가 있을 때 제거 후 재생성
DROP FUNCTION IF EXISTS public.get_admin_users();

CREATE OR REPLACE FUNCTION public.get_admin_users()
RETURNS TABLE (
  id uuid,
  email text,
  created_at timestamptz,
  last_sign_in_at timestamptz,
  display_name text,
  role text,
  avatar_url text,
  signup_provider text,
  linked_providers text,
  profile_status text,
  withdrawn_at timestamptz
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public, auth
AS $$
  SELECT
    u.id,
    u.email::text,
    u.created_at,
    u.last_sign_in_at,
    COALESCE(
      p.display_name,
      u.raw_user_meta_data->>'display_name',
      split_part(u.email::text, '@', 1),
      'User'
    ) AS display_name,
    ur.role,
    (u.raw_user_meta_data->>'avatar_url')::text AS avatar_url,
    COALESCE(u.raw_app_meta_data->>'provider', 'email') AS signup_provider,
    (SELECT string_agg(i.provider, ',' ORDER BY i.provider)
     FROM auth.identities i
     WHERE i.user_id = u.id) AS linked_providers,
    p.status AS profile_status,
    p.withdrawn_at AS withdrawn_at
  FROM auth.users u
  LEFT JOIN public.profiles p ON p.user_id = u.id
  LEFT JOIN public.user_roles ur ON ur.user_id = u.id
  WHERE EXISTS (
    SELECT 1 FROM public.user_roles r
    WHERE r.user_id = auth.uid() AND r.role IN ('super_admin', 'operator')
  )
  ORDER BY u.created_at DESC;
$$;

COMMENT ON FUNCTION public.get_admin_users() IS '관리자(super_admin/operator)만 호출 가능. 전체 사용자 목록(id, email, created_at, last_sign_in_at, display_name, role, avatar_url, signup_provider, linked_providers, profile_status, withdrawn_at) 반환. profile_status는 profiles.status(active/withdrawn). withdrawn_at은 탈퇴 시각.';

GRANT EXECUTE ON FUNCTION public.get_admin_users() TO authenticated;

-- =============================================================================
-- 관리자용: 가입 통계 (총 가입자 수, 이번 달 신규 가입 수) — 대시보드 "총 가입자" 카드용
-- =============================================================================

CREATE OR REPLACE FUNCTION public.get_admin_registration_stats()
RETURNS TABLE (total bigint, new_this_month bigint)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public, auth
AS $$
  SELECT
    (SELECT count(*)::bigint FROM auth.users) AS total,
    (SELECT count(*)::bigint FROM auth.users WHERE created_at >= date_trunc('month', now())) AS new_this_month
  WHERE EXISTS (
    SELECT 1 FROM public.user_roles r
    WHERE r.user_id = auth.uid() AND r.role IN ('super_admin', 'operator')
  );
$$;

COMMENT ON FUNCTION public.get_admin_registration_stats() IS '관리자(super_admin/operator)만 호출 가능. total=전체 가입자 수, new_this_month=이번 달 신규 가입 수.';

GRANT EXECUTE ON FUNCTION public.get_admin_registration_stats() TO authenticated;

-- =============================================================================
-- 관리자용: 최근 6개월 월별 신규 가입 수 — 대시보드 "총 가입자" 카드 막대 그래프용
-- =============================================================================

CREATE OR REPLACE FUNCTION public.get_admin_registration_monthly()
RETURNS TABLE (month_start date, cnt bigint)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public, auth
AS $$
  WITH m AS (SELECT generate_series(5, 0, -1) AS n)
  SELECT
    date_trunc('month', now() - (m.n || ' months')::interval)::date AS month_start,
    (SELECT count(*)::bigint FROM auth.users u
     WHERE u.created_at >= date_trunc('month', now() - (m.n || ' months')::interval)
       AND u.created_at < date_trunc('month', now() - (m.n || ' months')::interval) + interval '1 month'
    ) AS cnt
  FROM m
  WHERE EXISTS (
    SELECT 1 FROM public.user_roles r
    WHERE r.user_id = auth.uid() AND r.role IN ('super_admin', 'operator')
  )
  ORDER BY month_start ASC;
$$;

COMMENT ON FUNCTION public.get_admin_registration_monthly() IS '관리자(super_admin/operator)만 호출 가능. 최근 6개월(가장 오래된 달부터) 월별 신규 가입 수. month_start=해당 월 1일, cnt=가입자 수.';

GRANT EXECUTE ON FUNCTION public.get_admin_registration_monthly() TO authenticated;